package com.navi.loaddata.core.util.queryRunnerTest;


import com.navi.loaddata.core.mysqlEntity.EdaDiffDModel;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.io.InputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class QueryRunnerTest {
    public <T> List<T> query(Class<T> clazz, String sql) {
        return null;
    }

    /**
     * ResultSet: 结果集. 封装了使用 JDBC 进行查询的结果. 1. 调用 Statement 对象的 executeQuery(sql)
     * 可以得到结果集. 2. ResultSet 返回的实际上就是一张数据表. 有一个指针指向数据表的第一样的前面. 可以调用 next()
     * 方法检测下一行是否有效. 若有效该方法返回 true, 且指针下移. 相当于 Iterator 对象的 hasNext() 和 next()
     * 方法的结合体 3. 当指针对位到一行时, 可以通过调用 getXxx(index) 或 getXxx(columnName) 获取每一列的值.
     * 例如: getInt(1), getString("name") 4. ResultSet 当然也需要进行关闭.
     */
    public  List<EdaDiffDModel> query(String sql) throws Exception {
        List<EdaDiffDModel> list = new ArrayList<>();
        // 获取 id=4 的 customers 数据表的记录, 并打印

        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;

        try {
            // 1. 获取 Connection
            conn = JDBCTools.getConnection();
//            System.out.println(conn);

            // 2. 获取 Statement
            statement = conn.createStatement();
//            System.out.println(statement);

            // 3. 准备 SQL

            // 4. 执行查询, 得到 ResultSet
            rs = statement.executeQuery(sql);
//            System.out.println(rs);

            // 5. 处理 ResultSet
            while (rs.next()) {
                EdaDiffDModel edaDiffDModel = new EdaDiffDModel();
                edaDiffDModel.setShop(rs.getString(1));
                edaDiffDModel.setOpe(rs.getString(2));
                edaDiffDModel.setGlassId(rs.getString(3));
                edaDiffDModel.setEvtTimestamp(rs.getTimestamp(4));
                edaDiffDModel.setTimekey(rs.getString(5));

                list.add(edaDiffDModel);
            }
            return list;

        } catch (Exception e) {
//            e.printStackTrace();
            throw e;
        } finally {
            // 6. 关闭数据库资源.
            JDBCTools.release(rs, statement, conn);
        }

    }

    public List<EdaDiffDModel> query2(String sql) {
        List<EdaDiffDModel> list = new ArrayList<>();
        // 获取 id=4 的 customers 数据表的记录, 并打印

        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;

        try {
            // 1. 获取 Connection
            conn = JDBCTools.getConnection();
//            System.out.println(conn);

            // 2. 获取 Statement
            statement = conn.createStatement();
//            System.out.println(statement);

            // 3. 准备 SQL

            // 4. 执行查询, 得到 ResultSet
            rs = statement.executeQuery(sql);
//            System.out.println(rs);

            // 5. 处理 ResultSet
            while (rs.next()) {
                EdaDiffDModel e=new EdaDiffDModel();
                e.setGlassId(rs.getString(1));
                e.setOpe(rs.getString(2));
                e.setEvtTimestamp(rs.getTimestamp(3));
                list.add(e);
            }
return list;
        } catch (Exception e) {
//            throw e;
            e.printStackTrace();
        } finally {
            // 6. 关闭数据库资源.
            JDBCTools.release(rs, statement, conn);
        }
        return null;
    }


//    public static void main(String[] r){
//        String sql="SELECT /*+INDEX(SEG MES_PRODUCTHISTORY_PK)*/ SUBFACTORYNAME as shop," +
//                " OLDPROCESSOPERATIONNAME as ope," +
//                " PRODUCTNAME as glassId," +
//                " EVENTTIME as evtTimstamp," +
//                " TIMEKEY as timekey"+
//                " FROM MES_PRODUCTHISTORY SEG WHERE  1=1" +
//                " AND TIMEKEY>='20180701000000'" +
//                " AND TIMEKEY<'20180702000000'" +
//                " AND SUBFACTORYNAME ='ARRAY'" +
//                " AND EVENTNAME='TrackOut'  " +
//                " AND LINKKEY IS NOT NULL" +
//                " AND INSTR(OLDPROCESSOPERATIONNAME,'DUM')=0" +
//                " AND CASE WHEN LENGTH(OLDPROCESSOPERATIONNAME)>=6 THEN SUBSTR(OLDPROCESSOPERATIONNAME,6,6) ELSE 'X' END <>'D'" +
//                " AND OLDPROCESSOPERATIONNAME NOT IN ('A1100','C1100')" +
//                " and LENGTH(PRODUCTNAME)=10";
//        List<EdaDiffDModel> list = new ArrayList<>();
//        // 获取 id=4 的 customers 数据表的记录, 并打印
//
//        Connection conn = null;
//        Statement statement = null;
//        ResultSet rs = null;
//
//        try {
//            // 1. 获取 Connection
//            conn = JDBCTools.getConnection();
////            System.out.println(conn);
//
//            // 2. 获取 Statement
//            statement = conn.createStatement();
////            System.out.println(statement);
//
//            // 3. 准备 SQL
//
//            // 4. 执行查询, 得到 ResultSet
//            rs = statement.executeQuery(sql);
////            System.out.println(rs);
//
//            // 5. 处理 ResultSet
//            while (rs.next()) {
//                EdaDiffDModel edaDiffDModel = new EdaDiffDModel();
//                edaDiffDModel.setShop(rs.getString(1));
//                edaDiffDModel.setOpe(rs.getString(2));
//                edaDiffDModel.setGlassId(rs.getString(3));
//                edaDiffDModel.setEvtTimestamp(rs.getTimestamp(4));
//                edaDiffDModel.setTimekey(rs.getString(5));
//
//                list.add(edaDiffDModel);
//            }
//            System.out.print("**********************************:"+list.size());
////            return list;
//
//        } catch (Exception e) {
//            e.printStackTrace();
////            throw e;
//        } finally {
//            // 6. 关闭数据库资源.
//            JDBCTools.release(rs, statement, conn);
//        }
//    }


}
